package henu.dao.impl;

import henu.bean.Company;
import henu.bean.Group_Company;
import henu.dao.CompanyDao;
import henu.dao.factory.DaoFactory;
import henu.util.Dbcp;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;


public class CompanyDaoImpl implements CompanyDao{
  
	  public static void main(String[] args){
		  
		  CompanyDaoImpl cd = new CompanyDaoImpl();
		  List<Company> list = cd.findBelongid();
		  JSONArray json = JSONArray.fromObject(list);
		  System.out.println(json.toString());
		  
		  
		  
		  
		  }
		  
		  

	
	@Override
	public int save(Company c) {
		int result = 0;
		try {
			QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
			String sql = "insert into  Company(belongid,companyname,companytype,address,legalperson,businesslicense,organizationcode,contactphone,isexistcompany,createtime,businessrange,organizecard,contactaddress,registmoney,registeroffice,businesslimit,allowbusinesscard,postcode,basicaccoutopen,basicaccoutid,companyprincipal,memo1,memo2,memo3,memo4) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			System.out.println(sql);
			Object[] params = {c.getBelongid(),c.getCompanyname(),c.getCompanytype(),c.getAddress(),c.getLegalperson(),c.getBusinesslicense(),c.getOrganizationcode(),c.getContactphone(),c.getIsexistcompany(),c.getCreatetime(),c.getBusinessrange(),c.getOrganizecard(),c.getContactaddress(),c.getRegistmoney(),c.getRegisteroffice(),c.getBusinesslimit(),c.getAllowbusinesscard(),c.getPostcode(),c.getBasicaccoutopen(),c.getBasicaccoutid(),c.getCompanyprincipal(),c.getMemo1(),c.getMemo2(),c.getMemo3(),c.getMemo4()};
			result = qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int delete(String comid) {
		int result = 0;
		try {
			QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
			String sql = "delete from Company where comid = ?";
			result = qr.update(sql, comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int upadte(Company c) {
		// TODO Auto-generated method stub
		int result = 0;
		try {                                                                                                                                                                                                                 /*contactaddress,registmoney,registeroffice,businesslimit,allowbusinesscard,postcode,basicaccoutopen,basicaccoutid,companyprincipal,memo1,memo2,memo3,memo4*/
			QueryRunner qr =DaoFactory.getRunner();
			String sql = "update  company  set belongid = ?,companyname = ?,companytype = ?,address = ?,legalperson = ?,businesslicense = ?,organizationcode = ?,contactphone = ?,isexistcompany = ?,createtime = ?,businessrange = ?,organizecard = ?,contactaddress = ?,registmoney = ?,registeroffice = ? ,businesslimit = ? ,allowbusinesscard = ? ,postcode = ? ,basicaccoutopen = ? ,basicaccoutid = ? ,companyprincipal = ? , memo1 = ?,memo2 = ?,memo3 = ?,memo4 = ? where comid = ?";
			System.out.println(sql);
			Object[] params = {c.getBelongid(),c.getCompanyname(),c.getCompanytype(),c.getAddress(),c.getLegalperson(),c.getBusinesslicense(),c.getOrganizationcode(),c.getContactphone(),c.getIsexistcompany(),c.getCreatetime(),c.getBusinessrange(),c.getOrganizecard(),c.getContactaddress(),c.getRegistmoney(),c.getRegisteroffice(),c.getBusinesslimit(),c.getAllowbusinesscard(),c.getPostcode(),c.getBasicaccoutopen(),c.getBasicaccoutid(),c.getCompanyprincipal(),c.getMemo1(),c.getMemo2(),c.getMemo3(),c.getMemo4(),c.getComid()};
			result = qr.update(sql, params);
			JSONArray json = JSONArray.fromObject(params);
			System.out.println(json);
			System.out.println("result----->>>>>"+result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public Company findBy(String comid) {
		// TODO Auto-generated method stub
		Company tpc = new Company();
		try {
			QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
			String sql = "select * from Company where comid = ?";
		
			tpc = qr.query(sql, new BeanHandler<Company>(Company.class), comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tpc;
	}

	@Override
	public List<Company> findById(String comid) {
		// TODO Auto-generated method stub
		List<Company> list = new ArrayList<Company>();
		try {
			QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
			String sql = "select * from Company where comid = ?";
		
			list = qr.query(sql, new BeanListHandler<Company>(Company.class), comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Company> findAll(String order, String sort) {
		// TODO Auto-generated method stub
		List<Company> list = new ArrayList<Company>();
		QueryRunner qr = DaoFactory.getRunner();
		String sql = " select * from Company order by "+order+" "+sort+" ";
		try {
			list = qr.query(sql, new BeanListHandler<Company>(Company.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Company> findByProperty(String property, String key,
			String order, String sort, int start, int end) {
		// TODO Auto-generated method stub
		List<Company> list = new ArrayList<Company>();
		try {
			String sql="　　SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from Company where "+property+"='"+key+"'  order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
			QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
			list = qr.query(sql, new BeanListHandler<Company>(Company.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return list;
	}
	@Override
	public List<Company> findAllCompany() {
		// TODO Auto-generated method stub
		List<Company> list = new ArrayList<Company>();
		try {
			String sql="SELECT comid,companyname from Company";
			QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
			list = qr.query(sql, new BeanListHandler<Company>(Company.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return list;
	}

	@Override
	public List<Company> findBelongid() {
		// TODO Auto-generated method stub
		
		List<Company> list = null;
		try {
			QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
			String  sql = "select DISTINCT belongid from Company";
			list = qr.query(sql,new BeanListHandler<Company>(Company.class));
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		}
		return list;
	}

	@Override
	public List<Company> findCompany(String belongid) {
		// TODO Auto-generated method stub
		List<Company> list = null;
		try {
			QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
			String  sql = "select   companyname from Company where belongid= ? ";
			list = qr.query(sql,new BeanListHandler<Company>(Company.class),belongid);
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
		}
		return list;
	}
}
